perl parse_world.pl geonames_dd_dms_date_20050801.txt > all_world.sql

psql gnis -f all_world.sql > all_world.out

pgsql gnis -f gnis_country.dump

psql gnis -f  feature_codes.sql

psql gnis -f  subnational.sql

create index gnis_country_indx on gnis_country (country_code);
create index gnis_int_types_indx on gnis_int_types (type_code);
create index sub_nat_indx on gnis_intern_subnational (country_code,subnational_code);

create index gnis_country_indx2 on gnis_country ( bpchar(country_code) );
create index gnis_int_types_indx2 on gnis_int_types (bpchar(type_code));
create index sub_nat_indx2 on gnis_intern_subnational (bpchar(country_code),bpchar(subnational_code));

vacuum analyse gnis_country;
vacuum analyse gnis_intern_subnational;
vacuum analyse gnis_int_types;



alter table gnis_international add column country_name text;
alter table gnis_international add column sub_national text;
alter table gnis_international add column type text;
alter table gnis_international add column full_name_lc text;



update gnis_international set full_name_lc = lower(full_name_nd),
                              country_name = (select name_nd from gnis_country WHERE gnis_country.country_code =country),
                           sub_national = (select name_nd from gnis_intern_subnational WHERE country=country_code AND gnis_intern_subnational.subnational_code =gnis_international.sub_national_code ),
                           type = (select name_nd from gnis_int_types WHERE  gnis_int_types.type_code = gnis_international.feature_designition_code)
                          ;


alter table gnis_international add column est_pop int;
update gnis_international set est_pop = NULL;

cat *DECI.TXT > ../ALL_DECI.TXT

perl ../parse_state.pl <ALL_DECI.TXT  > all_state.sql
 psql gnis -f all_state.sql >& all_state.out

update gnis_usa set uniq_featcode = uniq_featcode+ 10000000;

alter table gnis_usa add column country text;
alter table gnis_usa add column country_name text;
alter table gnis_usa rename column  statecode to sub_national;
alter table gnis_usa rename column  feature_type to type;
alter table gnis_usa add column full_name_lc text;

update gnis_usa set country = 'US', country_name ='United States',  full_name_lc = lower(full_name_nd);


update gnis_usa set type = 'City/Town' where type ='ppl';

CREATE table gnis as
SELECT full_name_nd as full_name,
       full_name_lc,
       sub_national,
       country_name,
       country as country_code,
       est_pop,
       type,
       uniq_featcode,
       the_geom
       FROM gnis_international;

;

INSERT into gnis
SELECT  full_name_nd as full_name,
       full_name_lc,
       sub_national,
              country_name,
              country as country_code,
              est_pop,
              type,
              uniq_featcode,
              the_geom
       FROM gnis_usa
;

perl parse_pop.pl < dataen.txt > out.sql
in postgis:
   \i out.sql
   update gnis_pop set the_geom= setsrid(the_Geom,4326);
   create index gnis_pop_spatial on gnis_pop using gist (the_Geom gist_geometry_ops);
   vacuum analyse;
   insert into geometry_columns values ('','','gnis_pop','the_geom',2,4326,'POINT');